﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using EnvironmentManagerSystem.MODE;
using MySql.Data.MySqlClient;

namespace EnvironmentManagerSystem.BLL
{
    public class ExecutiveDepartmentService
    {
        #region ExecutiveDepartment增删改查
        //查询所有项目执行部门结果
        public static DataTable GetExecutiveDepartmentAll()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("select ed_Id,ed_Name from tbs001_executivedepartment");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        /// <summary>
        /// 删除项目执行部门
        /// </summary>
        /// <param name="ID">部门编号</param>
        /// <returns></returns>
        public static bool DeleteExecutiveDepartment(int ed_Id)
        {
            try
            {
                string strSql = string.Format(@"delete from `tbs001_executivedepartment` where ed_Id={0}", ed_Id);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 修改部门
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool UpdateExecutiveDepartment(ExecutiveDepartmentModels ed)
        {
            try
            {
                string strSql = string.Format(@"update `tbs001_executivedepartment` set ed_Name='{0}' where ed_Id={1}", ed.Ed_Name, ed.Ed_Id);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 添加部门
        /// </summary>
        /// <param name="ROLENAME">部门名称</param>
        /// <returns></returns>
        public static bool AddExecutiveDepartment(string ed_Name)
        {
            try
            {
                string strSql = string.Format(@"INSERT INTO tbs001_executivedepartment (ed_Name) VALUES ('{0}')", ed_Name);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region User查询所有
        //查询所有用户
        public static DataTable GetUserAll()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("SELECT * FROM tbs001_user WHERE (LOWER(RoleId) LIKE '%项目负责人%' OR LOWER(RoleId) LIKE '%部门负责人%' ) GROUP BY ID");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 获取功能列表
        /// </summary>string en_user,
        /// <returns></returns>
        public static UserDataCollection GetUserList(int en_Id)
        {
            try
            {
                string sql = "SELECT ed_user FROM tbs001_executivedepartment WHERE ed_id = " + en_Id + "";
                string ed_user = DbHelperMySQL.Query(sql).Tables[0].Rows[0]["ed_user"].ToString();
                UserDataCollection list = new UserDataCollection();
                string strSql = @"SELECT a.* FROM tbs001_user a,tbs001_executivedepartment b WHERE a.id IN (" + ed_user + ") AND b.ed_id=" + en_Id + " GROUP BY a.id";
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        UserData ud = new UserData();
                        ud.ID = (int)dr["ID"];
                        ud.UserName = dr["UserName"].ToString();
                        list.Add(ud);
                    }
                }
                return list;
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 添加权限
        /// </summary>
        /// <param name="RoleID"></param>
        /// <param name="FunctionID"></param>
        /// <returns></returns>
        public static bool InsertRoleFunction(int ed_Id, int ID)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_executivedepartment SET ed_User=INSERT(ed_User,CHAR_LENGTH(ed_User)+1,CHAR_LENGTH(ed_User)+{0},',{1}') WHERE ed_Id= '{2}'", ID.ToString().Length + 1, ID, ed_Id);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 删除权限
        /// </summary>
        /// <param name="RoleID"></param>
        /// <param name="FunctionID"></param>
        /// <returns></returns>
        public static bool DeleteRoleFunction(int ed_Id, int ID)
        {
            try
            {
                string sql = "SELECT ed_user FROM tbs001_executivedepartment WHERE ed_id = " + ed_Id + "";
                string ed_user = DbHelperMySQL.Query(sql).Tables[0].Rows[0]["ed_user"].ToString();
                ed_user = ed_user.Replace(ID + ",", "");
                string strSql = string.Format(@"UPDATE tbs001_executivedepartment SET ed_User='{0}' WHERE ed_Id= '{1}'", ed_user, ed_Id);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        /// <summary>
        /// 某用户是否属于某部门
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="Department"></param>
        /// <returns></returns>
        public static bool WhetherBelong(int UserID, string Department)
        {
            bool result = false;
            string strSql = string.Format(@"select ed_user from tbs001_executivedepartment where ed_Name='{0}'", Department);
            if (DbHelperMySQL.Query(strSql).Tables[0].Rows.Count > 0)
            {
                if (DbHelperMySQL.Query(strSql).Tables[0].Rows[0][0] != DBNull.Value)
                {
                    string[] ed_user = DbHelperMySQL.Query(strSql).Tables[0].Rows[0][0].ToString().Split(',');
                    foreach (string userid in ed_user)
                    {
                        if (UserID == int.Parse(userid))
                            result = true;
                    }
                }
            }
            return result;
        }
    }
}
